package com.demo2do.fms.scheduler.entity.appointment;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;
import com.demo2do.fms.scheduler.util.FormatUtils;

/**
 *
 * @author Ark
 *
 */
public class VehicleAccumulatedMileageSearch implements QueryBuilder {
    
    /** 运输公司 */
    private Long organizationId;
    
    /** 驳运车 */
    private Long vehicleId;
    
    /** 调度指令开始日期 */
    private String beginShipmentDate;
    
    /** 调度指令结束日期 */
    private String endShipmentDate;
    
    /** 报销开始日期 */
    private String beginReimburseDate;
    
    /** 报销结束日期 */
    private String endReimburseDate;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;
    
    private QueryFilter queryFilter;

    private QueryOrder queryOrder;
    
    /**
     * The default constructor
     */
    public VehicleAccumulatedMileageSearch() {
        queryOrder = new QueryOrder("a.vehicle_id", "DESC");
    }
    
    /**
     * construct search by appointment
     * 
     * @param appointment
     */
    public VehicleAccumulatedMileageSearch(VehicleMileageAppointment appointment) {
        this.organizationId = appointment.getOrganizationId();
        this.beginShipmentDate = appointment.getBeginShipmentDate();
        this.endShipmentDate = appointment.getEndShipmentDate();
        this.beginReimburseDate = appointment.getBeginReimburseDate();
        this.endReimburseDate = appointment.getEndReimburseDate();
        queryOrder = new QueryOrder("a.vehicle_id", "DESC");
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
        
            queryFilter = new QueryFilter().addEquals("a", "vehicle_id", vehicleId);
            
            if (StringUtils.isNotBlank(beginShipmentDate)) {
                queryFilter.addSentence("FC_GET_SHIP_DATE_BY_DSP_ID(c.id) >= TO_DATE('" + beginShipmentDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endShipmentDate)) {
                queryFilter.addSentence("FC_GET_SHIP_DATE_BY_DSP_ID(c.id) < TO_DATE('" + endShipmentDate.replace("-", "") + "', 'yyyymmddhh') + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
            queryFilter.addEquals("c", "organization_id", organizationId);
            
            if (StringUtils.isNotBlank(beginReimburseDate)) {
                queryFilter.addSentence("c.expense_reimburse_date >= TO_DATE('" + beginReimburseDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endReimburseDate)) {
                queryFilter.addSentence("c.expense_reimburse_date < TO_DATE('" + endReimburseDate.replace("-", "") + "', 'yyyymmdd') + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
        }
        
        return queryFilter;
        
    }
    
    /**
     * @return
     */
    public String buildCountTemplate() {
        String sentence = "SELECT COUNT(*) FROM vw_dispatch_order a, tbl_truck_transport_cost b, tbl_truck_dispatch_order c, vw_transportation_company d, vw_vehicle v WHERE a.id = b.dispatch_order_id AND a.dispatch_order_code = c.dispatch_order_code AND c.organization_id = d.id AND a.vehicle_id = v.id AND a.is_expense_reimbursed = 1" + getPreparedFilter(false).clauseSentence();
        return sentence;           
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT a.shipment_group_id AS shipmentGroupId, ")
                     .append("a.start_facility_name AS startFacilityName, ")
                     .append("a.end_facility_name AS endFacilityName, ")
                     .append("a.team_name AS teamName, ")
                     .append("d.organization_name AS organizationName, ")
                     .append("v.vehicle_name AS vehicleName, ")
                     .append("full_load_distance + b.empty_load_distance AS facilityDistance, ")
                     .append("full_load_distance AS fullLoadDistance, ")
                     .append("b.empty_load_distance AS emptyLoadDistance, ")
                     .append("CASE WHEN (b.full_load_distance + b.empty_load_distance) = 0 THEN b.full_load_distance ELSE (b.full_load_distance / (b.full_load_distance + b.empty_load_distance)) END AS fullLoadRate, ")
                     .append("CASE WHEN (b.full_load_distance + b.empty_load_distance) = 0 THEN b.empty_load_distance ELSE (b.empty_load_distance / (b.full_load_distance + b.empty_load_distance)) END AS emptyLoadRate, ")
                     .append("b.road_fee AS roadFee, ")
                     .append("(b.external_oil_fee + b.internal_oil_fee_compensation) AS totalOilFee, ")
                     .append("FC_GET_OTHER_FEE(a.id) AS otherFee, ")
                     .append("(b.internal_oil_usage - b.internal_oil_usage_compen) AS actualInternalOilUsage ")
             .append("FROM vw_dispatch_order a, tbl_truck_transport_cost b, tbl_truck_dispatch_order c, vw_transportation_company d, vw_vehicle v ")
             .append("WHERE a.id = b.dispatch_order_id AND a.dispatch_order_code = c.dispatch_order_code AND c.organization_id = d.id AND a.vehicle_id = v.id AND a.is_expense_reimbursed = 1")
             .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
        
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<String[]> buildRowMapper() {
        
        RowMapper<String[]> rowMapper = new RowMapper<String[]>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public String[] mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                String shipmentGroupId = rs.getString("shipmentGroupId");
                String startFacilityName = rs.getString("startFacilityName");
                String endFacilityName = rs.getString("endFacilityName");
                String teamName = rs.getString("teamName");
                String organizationName = rs.getString("organizationName");
                String vehicleName = rs.getString("vehicleName");
                String facilityDistance = FormatUtils.formatNumber(rs.getBigDecimal("facilityDistance"));
                String fullLoadDistance = FormatUtils.formatNumber(rs.getBigDecimal("fullLoadDistance"));
                String emptyLoadDistance = FormatUtils.formatNumber(rs.getBigDecimal("emptyLoadDistance"));
                String fullLoadRate = FormatUtils.formatNumber(rs.getBigDecimal("fullLoadRate"));
                String emptyLoadRate = FormatUtils.formatNumber(rs.getBigDecimal("emptyLoadRate"));
                String roadFee = FormatUtils.formatNumber(rs.getBigDecimal("roadFee"));
                String totalOilFee = FormatUtils.formatNumber(rs.getBigDecimal("totalOilFee"));
                String otherFee = FormatUtils.formatNumber(rs.getBigDecimal("otherFee"));
                String actualInternalOilUsage = FormatUtils.formatNumber(rs.getBigDecimal("actualInternalOilUsage"));
                
                String[] result = {organizationName, vehicleName, shipmentGroupId, startFacilityName, endFacilityName, facilityDistance, fullLoadDistance, fullLoadRate, 
                        emptyLoadDistance, emptyLoadRate, teamName, roadFee, totalOilFee, otherFee, actualInternalOilUsage}; 
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the vehicleId
     */
    public Long getVehicleId() {
        return vehicleId;
    }

    /**
     * @return the beginShipmentDate
     */
    public String getBeginShipmentDate() {
        return beginShipmentDate;
    }

    /**
     * @return the endShipmentDate
     */
    public String getEndShipmentDate() {
        return endShipmentDate;
    }

    /**
     * @return the beginReimburseDate
     */
    public String getBeginReimburseDate() {
        return beginReimburseDate;
    }

    /**
     * @return the endReimburseDate
     */
    public String getEndReimburseDate() {
        return endReimburseDate;
    }

    /**
     * @return
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }
    
    /**
     * @return
     */
    public Integer getEndIndex() {
        return endIndex;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param vehicleId the vehicleId to set
     */
    public void setVehicleId(Long vehicleId) {
        this.vehicleId = vehicleId;
    }

    /**
     * @param beginShipmentDate the beginShipmentDate to set
     */
    public void setBeginShipmentDate(String beginShipmentDate) {
        this.beginShipmentDate = beginShipmentDate;
    }

    /**
     * @param endShipmentDate the endShipmentDate to set
     */
    public void setEndShipmentDate(String endShipmentDate) {
        this.endShipmentDate = endShipmentDate;
    }

    /**
     * @param beginReimburseDate the beginReimburseDate to set
     */
    public void setBeginReimburseDate(String beginReimburseDate) {
        this.beginReimburseDate = beginReimburseDate;
    }

    /**
     * @param endReimburseDate the endReimburseDate to set
     */
    public void setEndReimburseDate(String endReimburseDate) {
        this.endReimburseDate = endReimburseDate;
    }

    /**
     * @param beginIndex
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }
    
    /**
     * @param endIndex
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }
    
}
